#!/usr/bin/env python
# -*- coding: utf-8 -*-

import sqlite3
import json
import time
import logging
import os
from typing import Dict, List, Optional, Any, Tuple

# Configure logging
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
)
logger = logging.getLogger("mqtt_web")

# SQLite database setup
DB_PATH = "mqtt_data.db"

class BaseRepository:
    """基础仓库类，提供数据库连接和基本操作"""
    
    @staticmethod
    def get_connection():
        """获取数据库连接"""
        try:
            # 设置超时参数，避免锁定
            conn = sqlite3.connect(DB_PATH, timeout=20)
            conn.execute("PRAGMA journal_mode=WAL")  # 使用WAL模式
            conn.execute("PRAGMA busy_timeout=5000")  # 设置忙等待超时
            return conn
        except Exception as e:
            logger.error(f"Error getting database connection: {e}")
            logger.exception(e)
            raise
    
    @staticmethod
    def execute_query(query: str, params: tuple = (), fetch_one: bool = False):
        """执行查询并返回结果"""
        try:
            conn = sqlite3.connect(DB_PATH)
            cursor = conn.cursor()
            cursor.execute(query, params)
            
            if fetch_one:
                result = cursor.fetchone()
            else:
                result = cursor.fetchall()
                
            conn.close()
            return result
        except Exception as e:
            logger.error(f"Database query error: {e}")
            return None
    
    @staticmethod
    def execute_update(query: str, params: tuple = ()):
        """执行更新操作，返回影响的行数"""
        try:
            conn = BaseRepository.get_connection()
            cursor = conn.cursor()
            logger.debug(f"Executing update query: {query} with params: {params}")
            cursor.execute(query, params)
            affected_rows = cursor.rowcount
            conn.commit()
            logger.debug(f"Update affected {affected_rows} rows")
            conn.close()
            return affected_rows
        except Exception as e:
            logger.error(f"Error executing update query: {e}")
            logger.error(f"Query: {query}")
            logger.error(f"Params: {params}")
            logger.exception(e)
            
            try:
                conn.rollback()
            except:
                pass
                
            try:
                conn.close()
            except:
                pass
                
            # 重新尝试一次
            try:
                conn = BaseRepository.get_connection()
                cursor = conn.cursor()
                logger.debug(f"Retrying update query: {query} with params: {params}")
                cursor.execute(query, params)
                affected_rows = cursor.rowcount
                conn.commit()
                logger.debug(f"Retry update affected {affected_rows} rows")
                conn.close()
                return affected_rows
            except Exception as retry_e:
                logger.error(f"Error retrying update query: {retry_e}")
                
                try:
                    conn.close()
                except:
                    pass
                
                return 0
    
    @staticmethod
    def execute_insert(query: str, params: tuple = ()):
        """执行插入操作并返回新插入的ID"""
        try:
            conn = sqlite3.connect(DB_PATH)
            cursor = conn.cursor()
            cursor.execute(query, params)
            conn.commit()
            last_id = cursor.lastrowid
            conn.close()
            return last_id
        except Exception as e:
            logger.error(f"Database insert error: {e}")
            return None

class MQTTRepository(BaseRepository):
    """MQTT消息仓库"""
    
    @staticmethod
    def save_mqtt_message(topic: str, payload: dict, timestamp: int):
        """保存MQTT消息"""
        query = "INSERT INTO mqtt_messages (topic, payload, timestamp) VALUES (?, ?, ?)"
        params = (topic, json.dumps(payload), timestamp)
        return MQTTRepository.execute_insert(query, params)
    
    @staticmethod
    def get_mqtt_messages(limit: int = 10):
        """获取最近的MQTT消息"""
        query = "SELECT topic, payload, timestamp FROM mqtt_messages ORDER BY timestamp DESC LIMIT ?"
        rows = MQTTRepository.execute_query(query, (limit,))
        
        messages = []
        if rows:
            for row in rows:
                messages.append({
                    "topic": row[0],
                    "payload": json.loads(row[1]),
                    "timestamp": row[2]
                })
        return messages

class LicensePlateRepository(BaseRepository):
    """车牌仓库"""
    
    @staticmethod
    def save_license_plate(plate_id: str, plate_number: str, status: str, timestamp: int):
        """保存车牌信息"""
        query = "INSERT OR REPLACE INTO license_plates (id, plate_number, status, timestamp) VALUES (?, ?, ?, ?)"
        params = (plate_id, plate_number, status, timestamp)
        return LicensePlateRepository.execute_insert(query, params)
    
    @staticmethod
    def get_license_plates():
        """获取所有车牌信息"""
        query = "SELECT id, plate_number, status, timestamp FROM license_plates"
        rows = LicensePlateRepository.execute_query(query)
        
        plates = {}
        if rows:
            for row in rows:
                plates[row[0]] = {
                    "id": row[0],
                    "plate_number": row[1],
                    "status": row[2],
                    "timestamp": row[3]
                }
        return plates
    
    @staticmethod
    def get_license_plate(plate_id: str):
        """获取特定车牌信息"""
        query = "SELECT id, plate_number, status, timestamp FROM license_plates WHERE id = ?"
        row = LicensePlateRepository.execute_query(query, (plate_id,), fetch_one=True)
        
        if row:
            return {
                "id": row[0],
                "plate_number": row[1],
                "status": row[2],
                "timestamp": row[3]
            }
        return None

class SensorRepository(BaseRepository):
    """传感器仓库"""
    
    @staticmethod
    def save_sensor_data(sensor_id: str, value: bool, timestamp: int):
        """保存传感器数据"""
        query = "INSERT OR REPLACE INTO sensors (id, value, timestamp) VALUES (?, ?, ?)"
        params = (sensor_id, 1 if value else 0, timestamp)
        return SensorRepository.execute_insert(query, params)
    
    @staticmethod
    def get_sensors():
        """获取所有传感器数据"""
        query = "SELECT id, value, timestamp FROM sensors"
        rows = SensorRepository.execute_query(query)
        
        sensors = {}
        if rows:
            for row in rows:
                sensors[row[0]] = bool(row[1])
        return sensors
    
    @staticmethod
    def get_sensor(sensor_id: str):
        """获取特定传感器数据"""
        query = "SELECT id, value, timestamp FROM sensors WHERE id = ?"
        row = SensorRepository.execute_query(query, (sensor_id,), fetch_one=True)
        
        if row:
            return {
                "id": row[0],
                "value": bool(row[1]),
                "timestamp": row[2]
            }
        return None

class ParkingSpotRepository(BaseRepository):
    """车位仓库"""
    
    @staticmethod
    def get_parking_spots():
        """获取所有车位信息"""
        query = """
        SELECT ps.id, ps.spot_number, ps.status, ps.sensor_id, s.value as sensor_value, ps.last_updated 
        FROM parking_spots ps
        LEFT JOIN sensors s ON ps.sensor_id = s.id
        """
        rows = ParkingSpotRepository.execute_query(query)
        
        spots = []
        if rows:
            for row in rows:
                spots.append({
                    "id": row[0],
                    "spot_number": row[1],
                    "status": row[2],
                    "sensor_id": row[3],
                    "is_occupied": bool(row[4]) if row[4] is not None else None,
                    "last_updated": row[5]
                })
        return spots
    
    @staticmethod
    def get_parking_spot(spot_id: int):
        """获取特定车位信息"""
        query = """
        SELECT ps.id, ps.spot_number, ps.status, ps.sensor_id, s.value as sensor_value, ps.last_updated 
        FROM parking_spots ps
        LEFT JOIN sensors s ON ps.sensor_id = s.id
        WHERE ps.id = ?
        """
        row = ParkingSpotRepository.execute_query(query, (spot_id,), fetch_one=True)
        
        if row:
            return {
                "id": row[0],
                "spot_number": row[1],
                "status": row[2],
                "sensor_id": row[3],
                "is_occupied": bool(row[4]) if row[4] is not None else None,
                "last_updated": row[5]
            }
        return None
    
    @staticmethod
    def update_parking_spot_status(spot_number: str, status: str, timestamp: int):
        """更新车位状态"""
        # 先检查车位是否存在
        check_query = "SELECT id FROM parking_spots WHERE spot_number = ?"
        spot = ParkingSpotRepository.execute_query(check_query, (spot_number,), fetch_one=True)
        
        if not spot:
            logger.error(f"尝试更新不存在的车位状态: spot_number={spot_number}")
            return 0
        
        # 先获取当前车位状态以便日志记录
        current_status_query = "SELECT status FROM parking_spots WHERE spot_number = ?"
        current_status_row = ParkingSpotRepository.execute_query(current_status_query, (spot_number,), fetch_one=True)
        current_status = current_status_row[0] if current_status_row else "未知"
        
        logger.info(f"更新车位状态: spot_number={spot_number}, current_status={current_status}, new_status={status}, timestamp={timestamp}")
        query = "UPDATE parking_spots SET status = ?, last_updated = ? WHERE spot_number = ?"
        params = (status, timestamp, spot_number)
        
        # 添加额外的日志
        logger.info(f"执行更新SQL: {query} with params: {params}")
        
        try:
            result = ParkingSpotRepository.execute_update(query, params)
            
            if result > 0:
                logger.info(f"车位状态更新成功: spot_number={spot_number}, 从 {current_status} 变更为 {status}")
                
                # 验证更新是否真的成功
                verify_query = "SELECT status FROM parking_spots WHERE spot_number = ?"
                verify_row = ParkingSpotRepository.execute_query(verify_query, (spot_number,), fetch_one=True)
                if verify_row:
                    actual_status = verify_row[0]
                    if actual_status == status:
                        logger.info(f"验证成功: 车位 {spot_number} 状态已正确更新为 {status}")
                    else:
                        logger.error(f"验证失败: 车位 {spot_number} 状态应为 {status} 但实际为 {actual_status}")
            else:
                logger.warning(f"车位状态更新失败: spot_number={spot_number}, status={status}, 无行被更新")
                
            return result
        except Exception as e:
            logger.error(f"更新车位状态时发生异常: {e}")
            logger.exception(e)
            return 0
    
    @staticmethod
    def get_parking_summary():
        """获取车位使用情况汇总"""
        query = """
        SELECT 
            SUM(CASE WHEN status = 'free' THEN 1 ELSE 0 END) as free_spots,
            SUM(CASE WHEN status = 'occupied' THEN 1 ELSE 0 END) as occupied_spots,
            COUNT(*) as total_spots
        FROM parking_spots
        """
        row = ParkingSpotRepository.execute_query(query, fetch_one=True)
        
        if row:
            return {
                "free_spots": row[0] or 0,
                "occupied_spots": row[1] or 0,
                "total_spots": row[2] or 0
            }
        return {"free_spots": 0, "occupied_spots": 0, "total_spots": 0}

class ReservationRepository(BaseRepository):
    """预约仓库"""
    
    @staticmethod
    def create_reservation(plate_number: str, spot_number: str, start_time: int, end_time: Optional[int] = None):
        """创建预约"""
        timestamp = int(time.time() * 1000)
        
        query = """
        INSERT INTO reservations 
        (plate_number, spot_number, start_time, end_time, status, created_at) 
        VALUES (?, ?, ?, ?, ?, ?)
        """
        # 新建预约的初始状态为"待入场"
        params = (plate_number, spot_number, start_time, end_time, "waiting_entry", timestamp)
        return ReservationRepository.execute_insert(query, params)
    
    @staticmethod
    def get_reservations(status: Optional[str] = None):
        """获取预约列表"""
        if status:
            query = "SELECT * FROM reservations WHERE status = ? ORDER BY created_at DESC"
            rows = ReservationRepository.execute_query(query, (status,))
        else:
            query = "SELECT * FROM reservations ORDER BY created_at DESC"
            rows = ReservationRepository.execute_query(query)
        
        reservations = []
        if rows:
            for row in rows:
                reservations.append({
                    "id": row[0],
                    "plate_number": row[1],
                    "spot_number": row[2],
                    "start_time": row[3],
                    "end_time": row[4],
                    "status": row[5],
                    "created_at": row[6]
                })
        return reservations
    
    @staticmethod
    def get_reservations_by_plate(plate_number: str):
        """根据车牌号获取预约"""
        query = "SELECT * FROM reservations WHERE plate_number = ? ORDER BY created_at DESC"
        rows = ReservationRepository.execute_query(query, (plate_number,))
        
        reservations = []
        if rows:
            for row in rows:
                reservations.append({
                    "id": row[0],
                    "plate_number": row[1],
                    "spot_number": row[2],
                    "start_time": row[3],
                    "end_time": row[4],
                    "status": row[5],
                    "created_at": row[6]
                })
        return reservations
    
    @staticmethod
    def get_reservation(reservation_id: int):
        """获取特定预约"""
        query = "SELECT * FROM reservations WHERE id = ?"
        row = ReservationRepository.execute_query(query, (reservation_id,), fetch_one=True)
        
        if row:
            return {
                "id": row[0],
                "plate_number": row[1],
                "spot_number": row[2],
                "start_time": row[3],
                "end_time": row[4],
                "status": row[5],
                "created_at": row[6]
            }
        return None
    
    @staticmethod
    def update_reservation_status(reservation_id: int, status: str, end_time: Optional[int] = None):
        """更新预约状态"""
        if end_time is None:
            query = "UPDATE reservations SET status = ? WHERE id = ?"
            params = (status, reservation_id)
        else:
            query = "UPDATE reservations SET status = ?, end_time = ? WHERE id = ?"
            params = (status, end_time, reservation_id)
            
        return ReservationRepository.execute_update(query, params) > 0
    
    @staticmethod
    def get_active_reservation_by_plate(plate_number: str):
        """获取车牌的活跃预约"""
        # 活跃预约包括"待入场"、"已入场"和"已出场"状态
        query = """
        SELECT * FROM reservations 
        WHERE plate_number = ? AND status IN ('waiting_entry', 'entered', 'exited') 
        ORDER BY created_at DESC LIMIT 1
        """
        logger.info(f"Searching for active reservation for plate {plate_number}")
        row = ReservationRepository.execute_query(query, (plate_number,), fetch_one=True)
        
        if row:
            logger.info(f"Found active reservation for plate {plate_number}: id={row[0]}, status={row[5]}")
            return {
                "id": row[0],
                "plate_number": row[1],
                "spot_number": row[2],
                "start_time": row[3],
                "end_time": row[4],
                "status": row[5],
                "created_at": row[6]
            }
        logger.info(f"No active reservation found for plate {plate_number}")
        return None
    
    @staticmethod
    def get_reservation_count():
        """获取预约总数"""
        query = "SELECT COUNT(*) FROM reservations"
        row = ReservationRepository.execute_query(query, fetch_one=True)
        return row[0] if row else 0

class IncomeRepository(BaseRepository):
    """收入仓库"""
    
    @staticmethod
    def create_income_record(reservation_id: int, plate_number: str, amount: float, start_time: int, end_time: int):
        """创建收入记录"""
        timestamp = int(time.time() * 1000)
        
        # 修改SQL语句，添加支付状态相关字段
        query = """
        INSERT INTO income_records 
        (reservation_id, plate_number, amount, start_time, end_time, created_at, paid, payment_method, payment_id, payment_time) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """
        params = (reservation_id, plate_number, amount, start_time, end_time, timestamp, 0, None, None, None)
        return IncomeRepository.execute_insert(query, params)
    
    @staticmethod
    def get_income_records():
        """获取收入记录"""
        query = "SELECT * FROM income_records ORDER BY created_at DESC"
        rows = IncomeRepository.execute_query(query)
        
        records = []
        if rows:
            for row in rows:
                records.append({
                    "id": row[0],
                    "reservation_id": row[1],
                    "plate_number": row[2],
                    "amount": row[3],
                    "start_time": row[4],
                    "end_time": row[5],
                    "created_at": row[6],
                    "paid": bool(row[7]) if len(row) > 7 else False,
                    "payment_method": row[8] if len(row) > 8 else None,
                    "payment_id": row[9] if len(row) > 9 else None,
                    "payment_time": row[10] if len(row) > 10 else None
                })
        return records
    
    @staticmethod
    def get_income_record(record_id: int):
        """获取单条收入记录"""
        query = "SELECT * FROM income_records WHERE id = ?"
        row = IncomeRepository.execute_query(query, (record_id,), fetch_one=True)
        
        if row:
            return {
                "id": row[0],
                "reservation_id": row[1],
                "plate_number": row[2],
                "amount": row[3],
                "start_time": row[4],
                "end_time": row[5],
                "created_at": row[6],
                "paid": bool(row[7]) if len(row) > 7 else False,
                "payment_method": row[8] if len(row) > 8 else None,
                "payment_id": row[9] if len(row) > 9 else None,
                "payment_time": row[10] if len(row) > 10 else None
            }
        return None
    
    @staticmethod
    def get_unpaid_bills(plate_number: str):
        """获取未支付的账单"""
        query = "SELECT * FROM income_records WHERE plate_number = ? AND (paid = 0 OR paid IS NULL) ORDER BY created_at DESC"
        rows = IncomeRepository.execute_query(query, (plate_number,))
        
        bills = []
        if rows:
            for row in rows:
                bills.append({
                    "id": row[0],
                    "reservation_id": row[1],
                    "plate_number": row[2],
                    "amount": row[3],
                    "start_time": row[4],
                    "end_time": row[5],
                    "created_at": row[6],
                    "paid": bool(row[7]) if len(row) > 7 else False
                })
        return bills
    
    @staticmethod
    def get_all_unpaid_bills():
        """获取所有未支付的账单"""
        query = """
        SELECT i.id, i.reservation_id, i.plate_number, i.amount, i.start_time, i.end_time, i.created_at, i.paid,
               (SELECT v.spot_number FROM vehicle_records v 
                WHERE v.plate_number = i.plate_number 
                AND v.entry_time = i.start_time 
                LIMIT 1) as spot_number
        FROM income_records i
        WHERE (i.paid = 0 OR i.paid IS NULL)
        ORDER BY i.created_at DESC
        """
        rows = IncomeRepository.execute_query(query)
        
        bills = []
        if rows:
            for row in rows:
                bills.append({
                    "id": row[0],
                    "reservation_id": row[1],
                    "plate_number": row[2],
                    "amount": row[3],
                    "entry_time": row[4],  # start_time 映射为 entry_time
                    "exit_time": row[5],   # end_time 映射为 exit_time
                    "created_at": row[6],
                    "paid": bool(row[7]) if len(row) > 7 else False,
                    "spot_number": row[8] if row[8] else "未知"  # 添加车位号
                })
                
            # 在日志中记录账单信息，方便调试
            logger.info(f"查询到 {len(bills)} 条未支付账单")
            for bill in bills:
                logger.info(f"账单ID: {bill['id']}, 车牌: {bill['plate_number']}, " +
                          f"车位: {bill['spot_number']}, 金额: {bill['amount']}")
        else:
            logger.info("未查询到未支付账单")
            
        return bills
    
    @staticmethod
    def update_payment_status(record_id: int, paid: bool, payment_method: str, payment_id: str, payment_time: int):
        """更新支付状态"""
        query = """
        UPDATE income_records 
        SET paid = ?, payment_method = ?, payment_id = ?, payment_time = ?
        WHERE id = ?
        """
        params = (1 if paid else 0, payment_method, payment_id, payment_time, record_id)
        return IncomeRepository.execute_update(query, params) > 0
    
    @staticmethod
    def get_total_income():
        """获取总收入"""
        query = "SELECT SUM(amount) FROM income_records WHERE paid = 1"
        row = IncomeRepository.execute_query(query, fetch_one=True)
        return row[0] if row and row[0] else 0

class VehicleRecordRepository(BaseRepository):
    """车辆记录仓库"""
    
    @staticmethod
    def create_vehicle_record(plate_number: str, spot_number: str, entry_time: int, status: str):
        """创建车辆记录"""
        timestamp = int(time.time() * 1000)
        
        query = """
        INSERT INTO vehicle_records 
        (plate_number, spot_number, entry_time, status, created_at) 
        VALUES (?, ?, ?, ?, ?)
        """
        params = (plate_number, spot_number, entry_time, status, timestamp)
        return VehicleRecordRepository.execute_insert(query, params)
    
    @staticmethod
    def update_vehicle_record(record_id: int, exit_time: int, status: str):
        """更新车辆记录"""
        query = "UPDATE vehicle_records SET exit_time = ?, status = ? WHERE id = ?"
        params = (exit_time, status, record_id)
        return VehicleRecordRepository.execute_update(query, params) > 0
    
    @staticmethod
    def get_vehicle_records():
        """获取车辆记录"""
        query = "SELECT * FROM vehicle_records ORDER BY created_at DESC"
        rows = VehicleRecordRepository.execute_query(query)
        
        records = []
        if rows:
            for row in rows:
                records.append({
                    "id": row[0],
                    "plate_number": row[1],
                    "spot_number": row[2],
                    "entry_time": row[3],
                    "exit_time": row[4],
                    "status": row[5],
                    "created_at": row[6]
                })
        return records
    
    @staticmethod
    def get_vehicle_records_by_plate(plate_number: str, limit: int = 10):
        """获取指定车牌号的车辆记录"""
        query = "SELECT * FROM vehicle_records WHERE plate_number = ? ORDER BY created_at DESC LIMIT ?"
        rows = VehicleRecordRepository.execute_query(query, (plate_number, limit))
        
        records = []
        if rows:
            for row in rows:
                records.append({
                    "id": row[0],
                    "plate_number": row[1],
                    "spot_number": row[2],
                    "entry_time": row[3],
                    "exit_time": row[4],
                    "status": row[5],
                    "created_at": row[6]
                })
        return records
    
    @staticmethod
    def get_active_vehicle_record(plate_number: str):
        """获取车牌的活跃车辆记录"""
        query = """
        SELECT * FROM vehicle_records 
        WHERE plate_number = ? AND status != 'exited' 
        ORDER BY created_at DESC LIMIT 1
        """
        row = VehicleRecordRepository.execute_query(query, (plate_number,), fetch_one=True)
        
        if row:
            return {
                "id": row[0],
                "plate_number": row[1],
                "spot_number": row[2],
                "entry_time": row[3],
                "exit_time": row[4],
                "status": row[5],
                "created_at": row[6]
            }
        return None

class PlateDetectionRepository(BaseRepository):
    """车牌识别记录仓库"""
    
    @staticmethod
    def create_plate_detection(plate_number: str, detection_time: int):
        """创建车牌识别记录"""
        timestamp = int(time.time() * 1000)
        query = """
        INSERT INTO plate_detections 
        (plate_number, detection_time, created_at) 
        VALUES (?, ?, ?)
        """
        params = (plate_number, detection_time, timestamp)
        return PlateDetectionRepository.execute_insert(query, params)
    
    @staticmethod
    def get_plate_detections(limit: int = 10):
        """获取车牌识别记录"""
        query = """
        SELECT id, plate_number, detection_time, created_at 
        FROM plate_detections 
        ORDER BY detection_time DESC
        LIMIT ?
        """
        rows = PlateDetectionRepository.execute_query(query, (limit,))
        
        detections = []
        if rows:
            for row in rows:
                detections.append({
                    "id": row[0],
                    "plate_number": row[1],
                    "detection_time": row[2],
                    "created_at": row[3]
                })
        return detections

class PricingRuleRepository(BaseRepository):
    """计费规则仓库"""
    
    @staticmethod
    def get_active_pricing_rule():
        """获取活跃的计费规则"""
        query = "SELECT id, name, free_duration, unit_duration, unit_price, is_active, created_at FROM pricing_rules WHERE is_active = 1 LIMIT 1"
        row = PricingRuleRepository.execute_query(query, fetch_one=True)
        
        if row:
            return {
                "id": row[0],
                "name": row[1],
                "free_duration": row[2],
                "unit_duration": row[3],
                "unit_price": row[4],
                "is_active": bool(row[5]),
                "created_at": row[6]
            }
        return None
    
    @staticmethod
    def get_pricing_rules():
        """获取所有计费规则"""
        query = "SELECT id, name, free_duration, unit_duration, unit_price, is_active, created_at FROM pricing_rules ORDER BY created_at DESC"
        rows = PricingRuleRepository.execute_query(query)
        
        rules = []
        if rows:
            for row in rows:
                rules.append({
                    "id": row[0],
                    "name": row[1],
                    "free_duration": row[2],
                    "unit_duration": row[3],
                    "unit_price": row[4],
                    "is_active": bool(row[5]),
                    "created_at": row[6]
                })
        return rules
    
    @staticmethod
    def create_pricing_rule(name: str, free_duration: int, unit_duration: int, unit_price: float, is_active: bool = False):
        """创建计费规则"""
        timestamp = int(time.time() * 1000)
        
        # 如果新规则是活跃的，先将所有规则设为非活跃
        if is_active:
            PricingRuleRepository.execute_update("UPDATE pricing_rules SET is_active = 0")
        
        query = """
        INSERT INTO pricing_rules 
        (name, free_duration, unit_duration, unit_price, is_active, created_at) 
        VALUES (?, ?, ?, ?, ?, ?)
        """
        params = (name, free_duration, unit_duration, unit_price, 1 if is_active else 0, timestamp)
        return PricingRuleRepository.execute_insert(query, params)
    
    @staticmethod
    def update_pricing_rule(rule_id: int, name: str, free_duration: int, unit_duration: int, unit_price: float, is_active: bool = False):
        """更新计费规则"""
        # 如果新规则是活跃的，先将所有规则设为非活跃
        if is_active:
            PricingRuleRepository.execute_update("UPDATE pricing_rules SET is_active = 0")
        
        query = """
        UPDATE pricing_rules 
        SET name = ?, free_duration = ?, unit_duration = ?, unit_price = ?, is_active = ? 
        WHERE id = ?
        """
        params = (name, free_duration, unit_duration, unit_price, 1 if is_active else 0, rule_id)
        return PricingRuleRepository.execute_update(query, params)
    
    @staticmethod
    def activate_pricing_rule(rule_id: int):
        """激活计费规则"""
        # 先将所有规则设为非活跃
        PricingRuleRepository.execute_update("UPDATE pricing_rules SET is_active = 0")
        
        # 激活指定规则
        query = "UPDATE pricing_rules SET is_active = 1 WHERE id = ?"
        return PricingRuleRepository.execute_update(query, (rule_id,)) 